package com.anolesoft.amljmes.screen.mapper;

import com.anolesoft.amljmes.screen.dto.MesOrderAssess;
import com.anolesoft.amljmes.screen.dto.MesOrderTimeoutLogs;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

@Mapper
public interface MesOrderTimeoutLogsMapper {
    // 根据机型查询中间表连接考核表,显示厂代号,在制数,待归档数,1-10,11-20,21-30,30以上
    @Select("select * from ( select t.jx,t.factory_id, t.timeout_type,t.cxtime,count(1) geshu from " +
            "( select t.jx,t.factory_id,case when t.timeout_type=2 then 0 else t.timeout_type end  timeout_type, " +
            "case when to_number(t.cxtime ) < 11 then 0" +
            " when to_number(t.cxtime )>=11 and to_number(t.cxtime )<21 then 1 " +
            " when to_number(t.cxtime )>=21 and to_number(t.cxtime )<31 then 2 " +
            " when to_number(t.cxtime )>=31 then 3 else to_number(t.cxtime ) end cxtime" +
            " from mes_order_timeout_logs t where t.cxtime is not null  ) t" +
            " group by t.jx,t.factory_id, t.timeout_type,t.cxtime )" +
            " where 1 = 1 and jx = #{jx} order by factory_id")
    List<MesOrderTimeoutLogs> querytongji(String jx);

    // 根据机型查询中间表连接考核表,显示厂代号,在制数,待归档数,1-10,11-20,21-30,30以上
    @Select("select * from ( select t.jx,t.factory_id, t.timeout_type,t.cxtime,count(1) geshu from " +
            "( select t.jx,t.factory_id,case when t.timeout_type=2 then 0 else t.timeout_type end  timeout_type, " +
            "case when to_number(t.cxtime ) < 11 then 0" +
            " when to_number(t.cxtime )>=11 and to_number(t.cxtime )<21 then 1 " +
            " when to_number(t.cxtime )>=21 and to_number(t.cxtime )<31 then 2 " +
            " when to_number(t.cxtime )>=31 then 3 else to_number(t.cxtime ) end cxtime" +
            " from mes_order_timeout_logs t where t.cxtime is not null  ) t" +
            " group by t.jx,t.factory_id, t.timeout_type,t.cxtime )" +
            " where 1 = 1 order by factory_id")
    List<MesOrderTimeoutLogs> querytongjiwu();

    // 详情页查询
    @SelectProvider(type= MesOrderTimeoutLogsMapper.createSQL.class, method = "querymingxi")
    List<MesOrderAssess> selectPageList(MesOrderTimeoutLogs mesOrderTimeoutLogs);
    public static class createSQL {
        public static String querymingxi(MesOrderTimeoutLogs mesOrderTimeoutLogs){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" *\n" +
                            "  from (select *\n" +
                            "          from (SELECT t.objuid,\n" +
                            "                       tt.assessment,\n" +
                            "                       tt.assess_state,\n" +
                            "                       tt.sweep_type,\n" +
                            "                       tt.reason,\n" +
                            "                       e1.name informant_user,\n" +
                            "                       to_char(tt.informant_time, 'yyyy/mm/dd') informant_time,\n" +
                            "                       e2.name pd_user,\n" +
                            "                       to_char(tt.pd_time, 'yyyy/mm/dd') pd_time,\n" +
                            "                       e3.name art_user,\n" +
                            "                       to_char(tt.art_time, 'yyyy/mm/dd') art_time,\n" +
                            "                       e4.name quality_user,\n" +
                            "                       to_char(tt.quality_time, 'yyyy/mm/dd') quality_time,\n" +
                            "                       e5.name assessment_user,\n" +
                            "                       to_char(tt.assessment_time, 'yyyy/mm/dd') assessment_time,\n" +
                            "                       t.FABRECORDID zzjltm,\n" +
                            "                       tt.versions,\n" +
                            "                       tt.nianyue,\n" +
                            "                       t.factory_id,\n" +
                            "                       t.jx,\n" +
                            "                       t.jc,\n" +
                            "                       t.partid,\n" +
                            "                       t.curgongxuname,\n" +
                            "                       to_char(t.sctime, 'yyyy/mm/dd') sctime,\n" +
                            "                       to_char(t.dqgxtime, 'yyyy/mm/dd') dqgxtime,\n" +
                            "                       t.cxtime,\n" +
                            "                       rownum num1\n" +
                            "                  from mes_order_timeout_logs t\n" +
                            "                  left join (select * from mes_order_assess where versions = 0) tt\n" +
                            "                    on t.fabrecordid = tt.zzjltm\n" +
                            "                  left join (select objuid, name from stf_employee) e1\n" +
                            "                    on tt.informant_user = e1.objuid\n" +
                            "                  left join (select objuid, name from stf_employee) e2\n" +
                            "                    on tt.pd_user = e2.objuid\n" +
                            "                  left join (select objuid, name from stf_employee) e3\n" +
                            "                    on tt.art_user = e3.objuid\n" +
                            "                  left join (select objuid, name from stf_employee) e4\n" +
                            "                    on tt.quality_user = e4.objuid\n" +
                            "                  left join (select objuid, name from stf_employee) e5\n" +
                            "                    on tt.assessment_user = e5.objuid\n" +
                            "                 where 1 = 1\n" +
                            "                   and TIMEOUT_TYPE != 3");
                    if(mesOrderTimeoutLogs.getJx()!=null&&!String.valueOf(mesOrderTimeoutLogs.getJx()).equals("")){
                        String jx = mesOrderTimeoutLogs.getJx().toUpperCase();
                        sql.append(" and t.jx = #{jx}");
                    }
                    if(mesOrderTimeoutLogs.getFlag()!=null&&!String.valueOf(mesOrderTimeoutLogs.getFlag()).equals("")){
                        String flag = mesOrderTimeoutLogs.getFlag();
                        // 1 1-10; 2 11-20; 3 21-30; 4 30+
                        if ("1".equals(flag)) {
                            sql.append(" and (to_number(t.cxtime) between 1 and 10)");
                        }
                        if ("2".equals(flag)) {
                            sql.append(" and (to_number(t.cxtime) between 11 and 20)");
                        }
                        if ("3".equals(flag)) {
                            sql.append(" and (to_number(t.cxtime) between 21 and 30)");
                        }
                        if ("4".equals(flag)) {
                            sql.append(" and (to_number(t.cxtime) > 30)");
                        }
                    }
                    if(mesOrderTimeoutLogs.getFactoryId()!=null&&!String.valueOf(mesOrderTimeoutLogs.getFactoryId()).equals("")){
                        String factoryId = mesOrderTimeoutLogs.getFactoryId();
                        sql.append(" and t.factory_id = #{factoryId}");
                    }
                    sql.append(" order by t.factory_id,t.fabrecordid) ");
                    if(mesOrderTimeoutLogs.getPageBug() != null){
                        Integer pageBug = mesOrderTimeoutLogs.getPageBug();
                        sql.append(" where num1<=#{pageBug})");
                    }
                    if(mesOrderTimeoutLogs.getPageSmall() != null){
                        Integer pageSmall = mesOrderTimeoutLogs.getPageSmall();
                        sql.append(" where num1>#{pageSmall}");
                    }
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    // 详情页查询总条数
    @SelectProvider(type= MesOrderTimeoutLogsMapper.createSQL1.class, method = "countmingxi")
    int countPageList(MesOrderTimeoutLogs mesOrderTimeoutLogs);
    public static class createSQL1 {
        public static String countmingxi(MesOrderTimeoutLogs mesOrderTimeoutLogs){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" count(1) " +
                            " from (SELECT t.objuid,rownum num1\n" +
                            "                  from mes_order_timeout_logs t\n" +
                            "                  left join (select * from mes_order_assess where versions = 0) tt\n" +
                            "                    on t.fabrecordid = tt.zzjltm" +
                            "                 where 1 = 1\n" +
                            "                   and TIMEOUT_TYPE != 3");
                    if(mesOrderTimeoutLogs.getJx()!=null&&!String.valueOf(mesOrderTimeoutLogs.getJx()).equals("")){
                        String jx = mesOrderTimeoutLogs.getJx().toUpperCase();
                        sql.append(" and t.jx = #{jx}");
                    }
                    if(mesOrderTimeoutLogs.getFlag()!=null&&!String.valueOf(mesOrderTimeoutLogs.getFlag()).equals("")){
                        String flag = mesOrderTimeoutLogs.getFlag();
                        // 1 1-10; 2 11-20; 3 21-30; 4 30+
                        if ("1".equals(flag)) {
                            sql.append(" and (to_number(t.cxtime) between 1 and 10)");
                        }
                        if ("2".equals(flag)) {
                            sql.append(" and (to_number(t.cxtime) between 11 and 20)");
                        }
                        if ("3".equals(flag)) {
                            sql.append(" and (to_number(t.cxtime) between 21 and 30)");
                        }
                        if ("4".equals(flag)) {
                            sql.append(" and (to_number(t.cxtime) > 30)");
                        }
                    }
                    if(mesOrderTimeoutLogs.getFactoryId()!=null&&!String.valueOf(mesOrderTimeoutLogs.getFactoryId()).equals("")){
                        String factoryId = mesOrderTimeoutLogs.getFactoryId();
                        sql.append(" and t.factory_id = #{factoryId}");
                    }
                    sql.append(" order by t.factory_id,t.fabrecordid) ");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    // 验证考核表中是否存在数据(根据制造记录条码,查询未定版数据)
    @Select("select objuid from mes_order_assess t where t.zzjltm = #{zzjltm} and t.versions = 0")
    String countkaohe(String zzjltm);

    // 向考核表中插入数据
    @Insert("insert into mes_order_assess(creat_user,creat_time,assessment,assess_state,sweep_type,reason," +
            "zzjltm,versions,factory_id,jx,jc,partid,curgongxuname,sctime,dqgxtime,cxtime," +
            "assessment_user,assessment_time)" +
            " values(#{creatUser},sysdate,#{assessment},#{assessState},#{sweepType},#{reason}," +
            "#{zzjltm},'0',#{factoryId},#{jx},#{jc},#{partid},#{curgongxuname},to_date(#{sctime},'yyyy/mm/dd')," +
            "to_date(#{dqgxtime},'yyyy/mm/dd'),#{cxtime},#{assessmentUser},sysdate)")
    int insertkaohe(MesOrderAssess mesOrderAssess);

    // 考核建议改变时改变考核表中的数据(根据制造记录条码)
    @Update("update mes_order_assess set assessment = #{assessment},update_user = #{updateUser},update_time = sysdate," +
            "assessment_user = #{assessmentUser},assessment_time = sysdate,assess_state = #{assessState} where ZZJLTM = #{zzjltm} and versions = '0'")
    int updatekaohe(MesOrderAssess mesOrderAssess);

    // 未扫码原因填报完成时(更改状态)
    @Update("update mes_order_assess set update_user = #{updateUser},update_time = sysdate," +
            "INFORMANT_USER = #{informantUser},INFORMANT_TIME = sysdate,assess_state = '2'," +
            "SWEEP_TYPE = #{sweepType},REASON = #{reason} " +
            "where ZZJLTM = #{zzjltm} and versions = '0'")
    int updatereason(MesOrderAssess mesOrderAssess);

    // 生产审批人
    @Update("update mes_order_assess set update_user = #{updateUser},update_time = sysdate," +
            "PD_USER = #{pdUser},PD_TIME = sysdate " +
            "where ZZJLTM = #{zzjltm} and versions = '0'")
    int updateshengchan(MesOrderAssess mesOrderAssess);

    // 技术审批人
    @Update("update mes_order_assess set update_user = #{updateUser},update_time = sysdate," +
            "ART_USER = #{artUser},ART_TIME = sysdate " +
            "where ZZJLTM = #{zzjltm} and versions = '0'")
    int updatejishu(MesOrderAssess mesOrderAssess);

    // 质量审批人(更改状态)
    @Update("update mes_order_assess set update_user = #{updateUser},update_time = sysdate," +
            "QUALITY_USER = #{qualityUser},QUALITY_TIME = sysdate,assess_state = '3' " +
            "where ZZJLTM = #{zzjltm} and versions = '0'")
    int updatezhiliang(MesOrderAssess mesOrderAssess);

    // 人员验证
    @Select("select OBJUID from stf_employee where eid = #{eid} and factory_id = #{factoryId}")
    String countpop(@Param("eid") String eid,@Param("factoryId")String factoryId);


}
